package cn.com.dhcc.turbo.dao.creatTable;

import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.CM;
import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.turbo.entity.creatTable.InsertData;

/**
 * 建测试表
 *
 * @2015年3月6日 @下午3:41:10
 * @author 蒋佩钊
 */
@Repository
public class CreatTableDao extends BaseDao{

	public void testCreatTableNoKeyTS(String num) throws DaoException {
		String sql = "";
		
		sql = "create table TEST_WITHOUT_KEY_TS_" + num + " "
			 +"( col_1  CHAR(1),"
			 +"  col_2  CHAR(32),"
			 +"  col_3  VARCHAR2(32),"
			 +"  col_4  NUMBER,"
			 +"  col_5  NUMBER(1),"
			 +"  col_6  NUMBER(10,2),"
			 +"  col_7  VARCHAR2(32),"
			 +"  col_8  VARCHAR2(32),"
			 +"  col_9  VARCHAR2(32),"
			 +"  col_10 DATE,"
			 +"  col_11 DATE,"
			 +"  col_12 TIMESTAMP(6),"
			 +"  col_13 TIMESTAMP(6)"
			 +")";
		CM.getDao().ddl("创建没有主键和时间戳的表", sql);
	}
	
	public void testCreatTableWithKey(String num) throws DaoException {
		String sql = "";
		
		sql = "create table TEST_WITH_KEY_" + num + " "
				+"( col_1  CHAR(1),"
				+"  col_2  CHAR(32) not null,"
				+"  col_3  VARCHAR2(32),"
				+"  col_4  NUMBER,"
				+"  col_5  NUMBER(1),"
				+"  col_6  NUMBER(10,2),"
				+"  col_7  VARCHAR2(32),"
				+"  col_8  VARCHAR2(32),"
				+"  col_9  VARCHAR2(32),"
				+"  col_10 DATE,"
				+"  col_11 DATE,"
				+"  col_12 TIMESTAMP(6),"
				+"  col_13 TIMESTAMP(6)"
				+")";
		CM.getDao().ddl("创建有主键,没有时间戳的表", sql);
		sql = "alter table TEST_WITH_KEY_" + num + " "
			 +"  add constraint PK_WITH_KEY_" + num + " primary key (COL_2)";
		CM.getDao().ddl("创建有主键,没有时间戳的表,主键", sql);
	}
	
	public void testCreatTableWithTS(String num) throws DaoException {
		String sql = "";
		
		sql = "create table TEST_WITH_TS_" + num + " "
				+"( col_1  CHAR(1),"
				+"  col_2  CHAR(32),"
				+"  col_3  VARCHAR2(32),"
				+"  col_4  NUMBER,"
				+"  col_5  NUMBER(1),"
				+"  col_6  NUMBER(10,2),"
				+"  col_7  VARCHAR2(32),"
				+"  col_8  VARCHAR2(32),"
				+"  col_9  VARCHAR2(32),"
				+"  col_10 DATE,"
				+"  col_11 DATE,"
				+"  col_12 TIMESTAMP(6) default sysdate,"
				+"  col_13 TIMESTAMP(6)"
				+")";
		CM.getDao().ddl("创建有时间戳,没有主键的表", sql);
	}
	
	public void testCreatTableWithKeyTS(String num) throws DaoException {
		String sql = "";
		
		sql = "create table TEST_WITH_KEY_TS_" + num + " "
				+"( col_1  CHAR(1),"
				+"  col_2  CHAR(32) not null,"
				+"  col_3  VARCHAR2(32),"
				+"  col_4  NUMBER,"
				+"  col_5  NUMBER(1),"
				+"  col_6  NUMBER(10,2),"
				+"  col_7  VARCHAR2(32),"
				+"  col_8  VARCHAR2(32),"
				+"  col_9  VARCHAR2(32),"
				+"  col_10 DATE,"
				+"  col_11 DATE,"
				+"  col_12 TIMESTAMP(6) default sysdate,"
				+"  col_13 TIMESTAMP(6)"
				+")";
		CM.getDao().ddl("创建有时间戳,没有主键的表", sql);
		sql = "alter table TEST_WITH_KEY_TS_" + num + " "
				+"  add constraint PK_WITH_KEY_TS_" + num + " primary key (COL_2)";
		CM.getDao().ddl("创建有时间戳,有主键的表,创建主键", sql);
	}

	public void testCreatTableUK(String num, String col) throws DaoException {
		String sql = "";
		sql = "alter table TEST_WITH_KEY_TS_" + num + " "
				+"  add constraint UK_WITH_KEY_TS_" + num + "_" + col + " unique (" + col + ")";
		CM.getDao().ddl("创建唯一键", sql);
	}

	public void testCreatTableFK(String num, String foreignCode, String col) throws DaoException {
		String sql = "";
		sql = "alter table TEST_WITH_KEY_TS_" + num + " "
				+"  add constraint FK_WITH_KEY_TS_" + num + foreignCode + " foreign key (" + col + ")"
				+"  references TEST_WITH_KEY_TS_" + foreignCode + " (" + col + ")";
		CM.getDao().ddl("创建外键", sql);
	}

	/**
	 * 向无外键表插入数据
	 * @param tableName 表明
	 * @param tmpData 数据
	 * @throws DaoException
	 */
	public void insertDataNoFK(String tableName, InsertData tmpData) throws DaoException {
		String sql = "";
		sql = "insert into " + tableName
			+ "  (col_1, col_2, col_3, col_4, col_5, col_6, col_7,"
			+ "   col_8, col_9, col_10, col_11, col_12, col_13) "
			+ "values "
			+ " ('" + tmpData.getCol_1() + "', '"
			+         tmpData.getCol_2() + "', '"
			+         tmpData.getCol_3() + "', "
			+         tmpData.getCol_4() + ", "
			+         tmpData.getCol_5() + ", "
			+         tmpData.getCol_6() + ", '"
			+         tmpData.getCol_7() + "', '"
			+         tmpData.getCol_8() + "', '"
			+         tmpData.getCol_9() + "', "
			+ "		  sysdate, "
			+ "		  sysdate, "
			+ "		  systimestamp, "
			+ "		  systimestamp)";
		CM.getDao().insert("插入无外键数据", sql, null);
	}

	public void updateData(String tableName, String colChange,
			String changeValue, String colWhere, String changeWhere) throws DaoException {
		String sql = "";
		sql = "update "+ tableName + " set " + colChange + " = '" + changeValue + "' where " + colWhere + changeWhere;
		CM.getDao().update("更新数据", sql, null);
	}

	public void deleteData(String tableName, String colWhere, String changeWhere) throws DaoException {
		String sql = "";
		sql = "delete from "+ tableName + " where " + colWhere + changeWhere;
		CM.getDao().delete("删除数据", sql, null);
	}
}
